﻿CREATE TABLE [dbo].[PiRole] (
    [Id]           INT            IDENTITY (1, 1) NOT NULL,
    [SystemId]     NVARCHAR (50)  NULL,
    [OrganizeId]   INT            NULL,
    [Category]     NVARCHAR (50)  NULL,
    [Code]         NVARCHAR (50)  NULL,
    [RealName]     NVARCHAR (200) NULL,
    [AllowEdit]    INT            DEFAULT ((1)) NOT NULL,
    [AllowDelete]  INT            DEFAULT ((1)) NOT NULL,
    [SortCode]     INT            NULL,
    [DeleteMark]   INT            DEFAULT ((0)) NOT NULL,
    [Enabled]      INT            DEFAULT ((1)) NOT NULL,
    [Description]  NVARCHAR (200) NULL,
    [CreateOn]     DATETIME       DEFAULT (getdate()) NOT NULL,
    [CreateUserId] NVARCHAR (50)  NULL,
    [CreateBy]     NVARCHAR (50)  NULL,
    [ModifiedOn]   DATETIME       NULL,
    [ModifyUserId] NVARCHAR (50)  NULL,
    [ModifiedBy]   NVARCHAR (50)  NULL,
    CONSTRAINT [PK_PIROLE] PRIMARY KEY NONCLUSTERED ([Id] ASC)
);


GO

/*
 * 功    能：记录数据操作日志(INSERT、UPDATE、DELETE)
 * 作    者：胡勇
 * 编写日期：'2012-04-17'
 * 版    本: V1.0
 * 修改记录:
 */
CREATE TRIGGER tr_PiRole_OperationLog
    ON PiRole
    AFTER INSERT,UPDATE,DELETE
AS
	SET NOCOUNT ON
    DECLARE @Data NVARCHAR(Max),
			@Type CHAR(6),
			@Table NVARCHAR(128),
			@Cols NVARCHAR(max),
			@Sql NVARCHAR(max),
			@UserName NVARCHAR(50),
			@PrimaryKeyColumn NVARCHAR(30) --主键列名
			
    /*提取表字段內容,這裡不做數據類型判斷*/
    
	SELECT  
			@PrimaryKeyColumn = 'Id',
			@UserName= 'CreateBy',
			@Table='PiRole',
            @Data='',
            @Cols=''
            
    SELECT @Cols=+@Cols+''' '+name+'=''+Convert(nvarchar(max),Isnull(Quotename('+name+',''"''),''null''))+'
    FROM sys.columns 
    WHERE object_id=object_id(@Table) 
    SET @Cols=LEFT(@Cols,len(@Cols)-1)
    
    
    /*判斷操作類型，Insert/Update/Delete*/
    
    IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
        SET @Type='Insert'
    ELSE If EXISTS(SELECT 1 FROM Inserted) AND EXISTS(SELECT 1 FROM deleted)
        SET @Type='Update'
    ELSE
        SET @Type='Delete'

    /*写入日志表*/
    Begin
        --读更新前后的数据
        If OBJECT_ID('tempdb..#TmpTrigger1') IS NOT NULL
                Drop table #TmpTrigger1
                
        --更新前的数据TriggerKeyFlag=0、更新后的数据 TriggerKeyFlag=1
        
        SELECT *,TriggerKeyFlag=0 INTO #TmpTrigger1 FROM deleted 
        UNION ALL
        SELECT *,TriggerKeyFlag=1 FROM inserted
        
        --插入主表记录        		
		--SET @Sql=N'INSERT INTO DbOperationLog(Operator,Note) VALUES('+@UserName+','+@Type+')'
		--EXEC sp_executesql @Sql	
		SET @Sql=N'INSERT INTO DbOperationLog(Operator,Note)
				    SELECT TOP 1 '+@UserName+',@Type FROM #TmpTrigger1'
		EXEC sp_executesql @Sql,N'@Type CHAR(6)',@Type
		
	
		
        /* 構造SQL語句,暫時不考慮以下情況：        
        
            1.這裡不考慮幷發的處理，所以取對應日誌主表(DbOperationLog)的ID時候，讀的是最新的ID,
            在目前環境中，幷發的可能性很小，要是以後應用于幷發環境，需要重新修改這一位置
            
            2.當日誌表在獨立一個庫時候，以下的語句不適用.
        */
        SET @Sql=N'INSERT INTO DbOperationDetailLog (DbOperationLogID,TableName,TablePrimaryKey,Description,OperationType,Flag) 
                        SELECT (SELECT TOP(1) ID FROM DbOperationLog ORDER BY Logdate DESC),
                                @table,'+@PrimaryKeyColumn+','+@Cols+',@Type,TriggerKeyFlag
                        FROM #TmpTrigger1'
        --執行SQL語句                                                        
        EXEC sp_executesql @Sql,N'@table nvarchar(128),@Type nvarchar(max)',@table,@Type
    End

GO
DISABLE TRIGGER [dbo].[tr_PiRole_OperationLog]
    ON [dbo].[PiRole];

